﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CXMLStudioIDE.DataAccessLayer
{
    public class SolutionManagerDAL : BaseDAL
    {
        public DataSet GetProjectsInSolution(int i_SolutionID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.GetProjectsInSolution", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_SolutionID", i_SolutionID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet GetProjectsInScopeAndContext()
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            string query = "Select * from dbo.vw_ProjectsInScopeAndContext order by ProjectName,ScopeName,ContextName";
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(query, con);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }
        public DataSet RemoveProjectFromSolution(int i_SolutionID, int i_ProjectID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.RemoveProjectFromSolution", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_SolutionID", i_SolutionID);
            da.SelectCommand.Parameters.AddWithValue("int_ProjectID", i_ProjectID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet GetEntitiesForProject(int i_ProjectID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.GetEntitiesForProject", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_ProjectID", i_ProjectID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet GetProjectByProjectOwner(string sZ_NetworkID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.GetProjectByProjectOwner", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("vch_UserID", sZ_NetworkID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet GetSolutionsForUser(string sZ_NetworkID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("Select * from dbo.vw_CXMLUserSolutions where NetworkUserID='" + sZ_NetworkID + "'", con);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet SaveNewSolution(string sZ_SolutionName, string sZ_NetworkUserId)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.SaveNewSolution", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("vch_NetworkUserID", sZ_NetworkUserId);
            da.SelectCommand.Parameters.AddWithValue("vch_SolutionName", sZ_SolutionName);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;

        }

        public DataSet SaveNewProject(int i_SolutionID, string sZ_ProjectName, int i_CXMLuserID, int i_Default, int i_ContextID, int i_ScopeID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.SaveNewProject", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_SolutionID", i_SolutionID);
            da.SelectCommand.Parameters.AddWithValue("vch_ProjectName", sZ_ProjectName);
            da.SelectCommand.Parameters.AddWithValue("int_CXMLUserID", i_CXMLuserID);
            da.SelectCommand.Parameters.AddWithValue("int_Default", i_Default);
            da.SelectCommand.Parameters.AddWithValue("int_Context", i_ContextID);
            da.SelectCommand.Parameters.AddWithValue("int_Scope", i_ScopeID);

            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;

        }

        public DataSet SaveNewProjectFromExisting(string vch_ProjectName,int i_SolutionID, int i_ExistingProjectID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.SaveNewProjectFromExisting", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_SolutionID", i_SolutionID);
            da.SelectCommand.Parameters.AddWithValue("int_ExistingProjectID", i_ExistingProjectID);
            da.SelectCommand.Parameters.AddWithValue("vch_NewProjectName", vch_ProjectName);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet AddExistingEntityVersionToProject(int i_ProjectID, int i_EntityTypeVersionID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.AddExistingEntityVersionToProject", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_ProjectID", i_ProjectID);
            da.SelectCommand.Parameters.AddWithValue("int_EntityTypeVersionsID", i_EntityTypeVersionID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }

        public DataSet RemoveEntityVersionFromProject(int int_ProjectID, int int_EntityTypeVersionsID)
        {
            DataSet DS = new DataSet();
            System.Data.SqlClient.SqlConnection con = GetConnection();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter("dbo.RemoveEntityVersionFromProject", con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            da.SelectCommand.Parameters.AddWithValue("int_ProjectID", int_ProjectID);
            da.SelectCommand.Parameters.AddWithValue("int_EntityTypeVersionsID", int_EntityTypeVersionsID);
            con.Open();
            da.Fill(DS);
            da.Dispose();
            con.Close();
            return DS;
        }
    }
}
